package com.ssy.lingxi.product.repository.commodity;

import com.ssy.lingxi.product.entity.commodity.CommodityGoodsAttribute;
import com.ssy.lingxi.product.model.resp.CommodityGoodsAttributeResponse;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

/**
 * 商品货品属性表持久化层
 *
 * @author jw.chen
 * @version 2.0.0
 * @date 2020/7/13
 */
public interface CommodityGoodsAttributeRepository extends JpaRepository<CommodityGoodsAttribute, Long>, JpaSpecificationExecutor<CommodityGoodsAttribute> {


    /**
     * 根据属性和属性值id查询
     *
     * @param customerAttributeIdList
     * @param customerAttributeValueIdList
     * @return
     */
    @Query(value = "select customer_attribute_id,customer_attribute_value_id  from product_commodity_goods_attribute where commodity_unit_price_and_pic_id in (select commodity_unit_price_and_pic_id from product_commodity_goods_attribute where customer_attribute_id in(:customerAttributeIdList) and customer_attribute_value_id in(:customerAttributeValueIdList)) and commodity_unit_price_and_pic_id in (select pcupp.id from product_commodity_unit_price_and_pic pcupp inner join product_commodity pc on pcupp.commodity_id = pc.id where pc.customer_category_id in (:customerCategoryId)) group by customer_attribute_id,customer_attribute_value_id order by customer_attribute_id", nativeQuery = true)
    List<Map<String,Long>> findByCustomerAttributeIdAndValueId(@Param("customerAttributeIdList") List<Long> customerAttributeIdList,
                                                                              @Param("customerAttributeValueIdList") List<Long> customerAttributeValueIdList,
                                                               @Param("customerCategoryId")List<Long> customerCategoryIdList);

    /**
     * 根据属性和属性值id查询
     *
     * @param customerAttributeIdList
     * @param customerAttributeValueIdList
     * @return
     */
    @Query(value = "select customer_attribute_id,customer_attribute_value_id  from product_commodity_goods_attribute where commodity_unit_price_and_pic_id in (select commodity_unit_price_and_pic_id from product_commodity_goods_attribute where customer_attribute_id in(:customerAttributeIdList) and customer_attribute_value_id in(:customerAttributeValueIdList))  group by customer_attribute_id,customer_attribute_value_id order by customer_attribute_id", nativeQuery = true)
    List<Map<String,Long>> findByCustomerAttributeIdAndValueId(@Param("customerAttributeIdList") List<Long> customerAttributeIdList,
                                                               @Param("customerAttributeValueIdList") List<Long> customerAttributeValueIdList);


    /**
     * 根据属性id查询
     *
     * @param customerAttributeIdList
     * @return
     */
    @Query(value = "select customer_attribute_id,customer_attribute_value_id  from product_commodity_goods_attribute where commodity_unit_price_and_pic_id in (select commodity_unit_price_and_pic_id from product_commodity_goods_attribute where customer_attribute_id in(:customerAttributeIdList)) and commodity_unit_price_and_pic_id in (select pcupp.id from product_commodity_unit_price_and_pic pcupp inner join product_commodity pc on pcupp.commodity_id = pc.id where pc.customer_category_id in (:customerCategoryId)) group by customer_attribute_id,customer_attribute_value_id order by customer_attribute_id", nativeQuery = true)
    List<Map<String,Long>> findByCustomerAttributeId(@Param("customerAttributeIdList") List<Long> customerAttributeIdList,@Param("customerCategoryId")List<Long> customerCategoryIdList);

    /**
     * 根据属性id查询
     *
     * @param customerAttributeIdList
     * @return
     */
    @Query(value = "select customer_attribute_id,customer_attribute_value_id  from product_commodity_goods_attribute where commodity_unit_price_and_pic_id in (select commodity_unit_price_and_pic_id from product_commodity_goods_attribute where customer_attribute_id in(:customerAttributeIdList))  group by customer_attribute_id,customer_attribute_value_id order by customer_attribute_id", nativeQuery = true)
    List<Map<String,Long>> findByCustomerAttributeId(@Param("customerAttributeIdList") List<Long> customerAttributeIdList);


    /**
     * 根据属性值id查询
     *
     * @param customerAttributeValueIdList
     * @return
     */
    @Query(value = "select customer_attribute_id,customer_attribute_value_id  from product_commodity_goods_attribute where commodity_unit_price_and_pic_id in (select commodity_unit_price_and_pic_id from product_commodity_goods_attribute where customer_attribute_value_id in(:customerAttributeValueIdList)) and commodity_unit_price_and_pic_id in (select pcupp.id from product_commodity_unit_price_and_pic pcupp inner join product_commodity pc on pcupp.commodity_id = pc.id where pc.customer_category_id in (:customerCategoryId)) group by customer_attribute_id,customer_attribute_value_id order by customer_attribute_id", nativeQuery = true)
    List<Map<String,Long>> findByCustomerAttributeIdValueId(@Param("customerAttributeValueIdList") List<Long> customerAttributeValueIdList,@Param("customerCategoryId")List<Long> customerCategoryIdList);

    /**
     * 根据属性值id查询
     *
     * @param customerAttributeValueIdList
     * @return
     */
    @Query(value = "select customer_attribute_id,customer_attribute_value_id  from product_commodity_goods_attribute where commodity_unit_price_and_pic_id in (select commodity_unit_price_and_pic_id from product_commodity_goods_attribute where customer_attribute_value_id in(:customerAttributeValueIdList))  group by customer_attribute_id,customer_attribute_value_id order by customer_attribute_id", nativeQuery = true)
    List<Map<String,Long>> findByCustomerAttributeIdValueId(@Param("customerAttributeValueIdList") List<Long> customerAttributeValueIdList);

    /**
     * 查询
     *
     * @return
     */
    @Query(value = "select customer_attribute_id,customer_attribute_value_id  from product_commodity_goods_attribute where commodity_unit_price_and_pic_id in (select commodity_unit_price_and_pic_id from product_commodity_goods_attribute) and commodity_unit_price_and_pic_id in (select pcupp.id from product_commodity_unit_price_and_pic pcupp inner join product_commodity pc on pcupp.commodity_id = pc.id where pc.customer_category_id in (:customerCategoryId)) group by customer_attribute_id,customer_attribute_value_id order by customer_attribute_id", nativeQuery = true)
    List<Map<String,Long>> findCommodityGoodsAttribute(@Param("customerCategoryId")List<Long> customerCategoryIdList);

    /**
     * 查询
     *
     * @return
     */
    @Query(value = "select customer_attribute_id,customer_attribute_value_id  from product_commodity_goods_attribute where commodity_unit_price_and_pic_id in (select commodity_unit_price_and_pic_id from product_commodity_goods_attribute)  group by customer_attribute_id,customer_attribute_value_id order by customer_attribute_id", nativeQuery = true)
    List<Map<String,Long>> findCommodityGoodsAttribute();

}
